

'''
-- 创建用户之间的关系表
CREATE TABLE user_relations (
    id SERIAL PRIMARY KEY,
    manager_id INT NOT NULL, -- 管理用户id
    subordinate_id INT UNIQUE NOT NULL, -- 下属用户id 一个用户只能有一个上级，
    FOREIGN KEY (manager_id) REFERENCES users(user_id),
    FOREIGN KEY (subordinate_id) REFERENCES users(user_id),
    CONSTRAINT unique_manager_subordinate UNIQUE (manager_id, subordinate_id),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

SELECT SETVAL('user_relations_id_seq',max(id),TRUE) FROM user_relations;

'''

from . import logger
import psycopg2

class UserRelationsTable:

    def __init__(self, connection, cursor):

        self.connection = connection
        self.cursor = cursor


    # 创建用户之间的关系
    def create_user_relation(self, manager_id, subordinate_id):
        try:
            self.cursor.execute("INSERT INTO user_relations (manager_id, subordinate_id) VALUES (%s, %s)", (manager_id, subordinate_id))
            logger.info(f"User relation between user {manager_id} and user {subordinate_id} created successfully.")
            return True, "User relation created successfully."
        # 唯一键冲突
        except psycopg2.errors.UniqueViolation as e:
            logger.warning(f"Error creating user relation between user {manager_id} and user {subordinate_id}: {e}")
            return False, "User relation already exists."
        
        except Exception as e:
            logger.error(f"Error creating user relation between user {manager_id} and user {subordinate_id}: {e}")
            return False, str(e)

    # 获取用户之间的关系
    def get_user_relation(self, manager_id, subordinate_id):
        try:
            self.cursor.execute("SELECT * FROM user_relations WHERE manager_id = %s AND subordinate_id = %s", (manager_id, subordinate_id))
            user_relation = self.cursor.fetchone()
            logger.info(f"Got user relation between user {manager_id} and user {subordinate_id} successfully.")
            return user_relation
        except Exception as e:
            logger.error(f"Error getting user relation between user {manager_id} and user {subordinate_id}: {e}")
            return None

    # 获取用户的下属
    def get_subordinates_by_manager(self, manager_id):
        try:
            self.cursor.execute("SELECT * FROM user_relations WHERE manager_id = %s", (manager_id,))
            subordinates = self.cursor.fetchall()
            logger.info(f"Got subordinates of user {manager_id} successfully.")
            return subordinates
        except Exception as e:
            logger.error(f"Error getting subordinates of user {manager_id}: {e}")
            return None

    # 获取用户的上级
    def get_manager_by_subordinate(self, subordinate_id):
        try:
            self.cursor.execute("SELECT * FROM user_relations WHERE subordinate_id = %s", (subordinate_id,))
            manager = self.cursor.fetchone()
            logger.info(f"Got manager of user {subordinate_id} successfully.")
            return manager
        except Exception as e:
            logger.error(f"Error getting manager of user {subordinate_id}: {e}")
            return None

    # 删除用户之间的关系
    def delete_user_relation(self, manager_id, subordinate_id):
        try:
            self.cursor.execute("DELETE FROM user_relations WHERE manager_id = %s AND subordinate_id = %s", (manager_id, subordinate_id))
            logger.info(f"User relation between user {manager_id} and user {subordinate_id} deleted successfully.")
            return True
        except Exception as e:
            logger.error(f"Error deleting user relation between user {manager_id} and user {subordinate_id}: {e}")
            return False


    
